﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace CsharpCodeGenerator
{
    class SqlGenerator
    {
        public static string Author
        {
            get
            {
                return Session.LoadFromSession("AuthorName").ToString();
            }
        }

        public static bool FieldAllowNull
        {
            get
            {
                return (bool)Session.LoadFromSession("NullParamDefaultValues");
            }
        }
        #region "Stored Procedure Generation Methods"

        public static string CreateDeleteRowSP(string spName, string TableName, List<Sql> Columns)
        {
            string Parameters = string.Empty;
            string Params = string.Empty;
            string Values = string.Empty;

            foreach (var item in Columns)
            {
                if (item.TableName == TableName)
                {
                    if (int.Parse(item.IsIdentity) != 0 || int.Parse(item.IsIndex) != 0)
                    {
                        if (item.DataType.ToLower().Contains("char"))
                        {
                            string Length = (item.CharLengh.Equals("-1") ? "MAX" : item.CharLengh);

                            bool NullParamDefaultValues = FieldAllowNull;
                            if (item.IsNullable.ToLower() == "yes" && NullParamDefaultValues == true)
                            {
                                Parameters += string.Format("@{0} {1}({2}) = NULL,{3}", item.ColumnName, item.DataType, Length, Environment.NewLine);
                            }
                            else
                                Parameters += string.Format("@{0} {1}({2}),{3}", item.ColumnName, item.DataType, Length, Environment.NewLine);
                        }
                        else if (item.DataType.ToLower().Contains("numeric"))
                        {
                            Parameters += string.Format("numeric({0},{1}),{2}", item.NumericPercision, item.NumericScale, Environment.NewLine);
                        }
                        else
                        {
                            Parameters += string.Format("@{0} {1},{2}", item.ColumnName, item.DataType, Environment.NewLine);
                        }
                        Params += string.Format("[{0}] = @{0}, ",item.ColumnName);
                        
                    }
                }
            }

            Parameters = Parameters.Length > 2 ? Parameters.Remove(Parameters.Length - 3, 3) : "";
            Params = Params.Length > 1 ? Params.Remove(Params.Length - 2, 2) : "";

            Params = Params.Replace(",", " AND");

            string Code = string.Format(SqlScriptHelper.DropPorcedure, spName);
            Code += string.Format(SqlScriptHelper.SpDescription, spName, Author, DateTime.Now, "This SP delete specify row from " + TableName + " table");
            Code += string.Format(SqlScriptHelper.DeleteRow, spName, Parameters, TableName, Params);

            return Code;
        }

        public static string CreateInsertSP(string spName, string TableName, List<Sql> Columns)
        {
            string Parameters = string.Empty;
            string Params = string.Empty;
            string Values = string.Empty;

            foreach (var item in Columns)
            {
                if (item.TableName == TableName)
                {
                    if (int.Parse(item.IsIdentity) == 0)
                    {
                        if (item.DataType.ToLower().Contains("char"))
                        {
                            string Length = (item.CharLengh.Equals("-1") ? "MAX" : item.CharLengh);

                            bool NullParamDefaultValues = FieldAllowNull;
                            if (item.IsNullable.ToLower() == "yes" && NullParamDefaultValues == true)
                            {
                                Parameters += string.Format("@{0} {1}({2}) = NULL,{3}", item.ColumnName, item.DataType, Length,Environment.NewLine);
                            }
                            else
                                Parameters += string.Format("@{0} {1}({2}),{3}", item.ColumnName, item.DataType, Length,Environment.NewLine);
                        }
                        else if (item.DataType.ToLower().Contains("numeric"))
                        {
                            Parameters += string.Format("numeric({0},{1}),{2}", item.NumericPercision, item.NumericScale,Environment.NewLine);
                        }
                        else
                        {
                            Parameters += string.Format("@{0} {1},{2}", item.ColumnName, item.DataType,Environment.NewLine);
                        }
                        Params += string.Format("[{0}], ", item.ColumnName);
                        Values += string.Format("@{0}, ", item.ColumnName);
                    }
                }
            }

            Parameters = Parameters.Remove(Parameters.Length - 3, 3);
            Params = Params.Remove(Params.Length - 2, 2);
            Values = Values.Remove(Values.Length - 2, 2);
           
            string Code = string.Format(SqlScriptHelper.DropPorcedure, spName);
            Code += string.Format(SqlScriptHelper.SpDescription, spName, Author, DateTime.Now, "This SP Inserts value to " + TableName + " table");
            Code += string.Format(SqlScriptHelper.InsertRow, spName, Parameters, TableName, Params, Values);

            return Code;
        }

        public static string CreateSelectAllSP(string spName, string TableName, List<Sql> Columns)
        {
            string SQL = string.Empty;

            StringBuilder sb = new StringBuilder();
            sb.AppendLine(String.Format(SqlScriptHelper.DropPorcedure, spName));
            sb.AppendLine(string.Format(SqlScriptHelper.SpDescription, spName, Author, DateTime.Now, "Select all rows form "+TableName));
            string Params = "";
            foreach (var item in Columns)
            {
                if (item.TableName == TableName)
                {
                    if (int.Parse(item.IsIdentity) != 0 || int.Parse(item.IsIndex) != 0)
                    {
                        Params += string.Format("{0}, ", item.ColumnName);
                    }
                }
            }

            Params = Params.Length > 1 ? Params.Remove(Params.Length - 2, 2) : "";

            sb.AppendFormat(SqlScriptHelper.SelectAll.Replace("order by ID", "order by " + Params), spName, TableName);

            return sb.ToString();
        }

        public static string CreateSelectRowSP(string spName, string TableName, List<Sql> Columns)
        {
            string Parameters = string.Empty;
            string Params = string.Empty;

            foreach (var item in Columns)
            {
                if(item.TableName == TableName)
                {
                    if (int.Parse(item.IsIndex) != 0)
                    {
                        if (item.DataType.ToLower().Contains("char"))
                        {
                            string Length = (item.CharLengh.Equals("-1") ? "MAX" : item.CharLengh);
                            Parameters += string.Format("@{0} {1}({2}),",item.ColumnName,item.DataType,Length);
                        }
                        else
                        {
                            Parameters +=string.Format("@{0} {1},{2}",item.ColumnName, item.DataType,Environment.NewLine);
                        }
                        Params += string.Format("[{0}]=@{0} and ", item.ColumnName);
                    }
                }
            }
            //Parameters = (Parameters.Length > 3 ? Parameters.Remove(Parameters.Length - 3, 3) : "");
            Parameters = Parameters.Trim();
            if (Parameters.Length > 1 && Parameters[Parameters.Length - 1] == ',')
            {
                Parameters = Parameters.Substring(0, Parameters.Length - 1);
                Parameters += Environment.NewLine;
            }
                
            Params = Params.Length > 4 ? Params.Remove(Params.Length - 5, 5) : "";

            string Code = string.Format(SqlScriptHelper.DropPorcedure, spName);
            Code += string.Format(SqlScriptHelper.SpDescription, spName, Author, DateTime.Now, "This SP select a specify row from "+TableName);
            Code += string.Format(SqlScriptHelper.SelectRow, spName, Parameters, TableName, Params);
            return Code;
        }

        public static string CreateSelectByForeignKeySP(string spName, string TableName, List<Sql> Columns)
        {

            string ParamsFK = "";
            foreach (var item in Columns)
            {
                if (item.TableName == TableName)
                {
                    if (int.Parse(item.IsIdentity) != 0 || int.Parse(item.IsIndex) != 0)
                    {
                        ParamsFK += string.Format("{0}, ", item.ColumnName);
                    }
                }
            }

            ParamsFK = ParamsFK.Length > 1 ? ParamsFK.Remove(ParamsFK.Length - 2, 2) : "";


            string Code ="";
            foreach (var item in Columns)
            {
                if(item.TableName == TableName)
                {
                    if (item.IsForeignKey == null)
                        continue;
                    if (int.Parse(item.IsForeignKey) != 0 )
                    {
                        string Parameters = string.Empty;
                        string Params = string.Empty;
                        if (item.DataType.ToLower().Contains("char"))
                        {
                            string Length = (item.CharLengh.Equals("-1") ? "MAX" : item.CharLengh);
                            Parameters += string.Format("@{0} {1}({2}),",item.ColumnName,item.DataType,Length);
                        }
                        else
                        {
                            Parameters +=string.Format("@{0} {1},{2}",item.ColumnName, item.DataType,Environment.NewLine);
                        }
                        Params += string.Format("[{0}]=@{0} and ", item.ColumnName);
                        string strName = spName + "ByForeignKey_" + item.ColumnName;

                        Parameters = (Parameters.Length > 3 ? Parameters.Remove(Parameters.Length - 3, 3) : "");
                        Params = Params.Length > 4 ? Params.Remove(Params.Length - 5, 5) : "";

                        Code += string.Format(SqlScriptHelper.DropPorcedure, strName);
                        Code += string.Format(SqlScriptHelper.SpDescription, strName, Author, DateTime.Now, "This SP select a specify row from " + TableName);
                        Code += string.Format(SqlScriptHelper.SelectRowSP.Replace("order by ID", "order by " + ParamsFK), strName, Parameters, TableName, Params);
                    }
                }
            }
            string strNameAll = spName+"ByForeignKey";
            string ParametersTemp = string.Empty;
            string Params1 = string.Empty;
            foreach (var item in Columns)
            {
                if (item.TableName == TableName)
                {
                    if (item.IsForeignKey == null)
                        continue;
                    if (int.Parse(item.IsForeignKey) != 0)
                    {
                        
                        
                        if (item.DataType.ToLower().Contains("char"))
                        {
                            string Length = (item.CharLengh.Equals("-1") ? "MAX" : item.CharLengh);
                            ParametersTemp += string.Format("@{0} {1}({2}),", item.ColumnName, item.DataType, Length);
                        }
                        else
                        {
                            ParametersTemp += string.Format("@{0} {1},{2}", item.ColumnName, item.DataType, Environment.NewLine);
                        }
                        Params1 += string.Format("[{0}]=@{0} and ", item.ColumnName);
                        strNameAll = strNameAll + "_" + item.ColumnName;
                    }
                    
                }
            }
            Params1 = Params1.Length > 4 ? Params1.Remove(Params1.Length - 5, 5) : "";
            ParametersTemp = (ParametersTemp.Length > 3 ? ParametersTemp.Remove(ParametersTemp.Length - 3, 3) : "");
            if (ParametersTemp == "" || Code.IndexOf(strNameAll) > -1)
                return Code;
            Code += string.Format(SqlScriptHelper.DropPorcedure, strNameAll);
            Code += string.Format(SqlScriptHelper.SpDescription, strNameAll, Author, DateTime.Now, "This SP select a specify row from " + TableName);


            Code += string.Format(SqlScriptHelper.SelectRow.Replace("order by ID", "order by " + ParamsFK), strNameAll, ParametersTemp, TableName, Params1);

            return Code;
        }
        public static string CreateUpdateSP(string spName, string TableName, List<Sql> Columns)
        {
            string Parameters = string.Empty;
            string Params = string.Empty;
            string Where = string.Empty;

            foreach (var item in Columns)
            {
                if (item.TableName == TableName)
                {
                    if (int.Parse(item.IsIdentity) != 0 || int.Parse(item.IsIndex) != 0)
                    {
                        if (item.DataType.ToLower().Contains("char"))
                        {
                            string Length = (item.CharLengh.Equals("-1") ? "MAX" : item.CharLengh);

                            bool NullParamDefaultValues = FieldAllowNull;
                            if (item.IsNullable.ToLower() == "yes" && NullParamDefaultValues == true)
                            {
                                Parameters += string.Format("@{0} {1}({2}) = NULL,{3}", item.ColumnName, item.DataType, Length, Environment.NewLine);
                            }
                            else
                                Parameters += string.Format("@{0} {1}({2}),{3}", item.ColumnName, item.DataType, Length, Environment.NewLine);
                        }
                        else if (item.DataType.ToLower().Contains("numeric"))
                        {
                            Parameters += string.Format("numeric({0},{1}),{2}", item.NumericPercision, item.NumericScale, Environment.NewLine);
                        }
                        else
                        {
                            Parameters += string.Format("@{0} {1},{2}", item.ColumnName, item.DataType, Environment.NewLine);
                        }
                        Where += string.Format("[{0}] = @{0}, ", item.ColumnName);
                    }
                    else

                    if (int.Parse(item.IsIdentity) == 0)
                    {
                        if (item.DataType.ToLower().Contains("char"))
                        {
                            string Length = (item.CharLengh.Equals("-1") ? "MAX" : item.CharLengh);

                            bool NullParamDefaultValues = FieldAllowNull;
                            if (item.IsNullable.ToLower() == "yes" && NullParamDefaultValues == true)
                            {
                                Parameters += string.Format("@{0} {1}({2}) = NULL,{3}", item.ColumnName, item.DataType, Length, Environment.NewLine);
                            }
                            else
                                Parameters += string.Format("@{0} {1}({2}),{3}", item.ColumnName, item.DataType, Length, Environment.NewLine);
                        }
                        else if (item.DataType.ToLower().Contains("numeric"))
                        {
                            Parameters += string.Format("numeric({0},{1}),{2}", item.NumericPercision, item.NumericScale, Environment.NewLine);
                        }
                        else
                        {
                            Parameters += string.Format("@{0} {1},{2}", item.ColumnName, item.DataType, Environment.NewLine);
                        }
                        Params += string.Format("[{0}] = @{0}, ", item.ColumnName);
                    }
                }
            }

            Parameters = Parameters.Remove(Parameters.Length - 3, 3);
            if(Params.Length>2)
                Params = Params.Remove(Params.Length - 2, 2);

            Where = Where.Length > 1 ? Where.Remove(Where.Length - 2, 2) : "";

            Where = Where.Replace(",", " AND");

            string Code = string.Format(SqlScriptHelper.DropPorcedure, spName);
            Code += string.Format(SqlScriptHelper.SpDescription, spName, Author, DateTime.Now, "This SP updates " + TableName + " table rows.");
            Code += string.Format(SqlScriptHelper.UpdateRow, spName, Parameters, TableName, Params, Where);

            return Code;
        }
        #endregion

    }
}
